package com.xquant.trade.utils;

import cn.hutool.core.map.MapUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.fastjson2.JSONObject;
import com.xquant.common.security.utils.SecurityUtils;
import com.xquant.trade.account.entity.Account;
import com.xquant.trade.account.mapper.AccountMapper;
import com.xquant.trade.systerm.mapper.InstrumentMapper;
import com.xquant.trade.trade.mapper.TradeMapper;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.text.SimpleDateFormat;
import java.util.*;

@Component
public class TransferUtil {

    @Autowired
    private AccountMapper accountMapper;
    @Autowired
    private TradeMapper tradeMapper;

    @Autowired
    private InstrumentMapper instrumentMapper;

    private static Map<String, List> cacheMap = new HashMap();

    public static Map getCacheMap() {
        return cacheMap;
    }

    public CommResponse getSheetData(Workbook wb, String sheetName, Map param) {
        if (wb == null) {
            return null;
        }
        Sheet sheet = wb.getSheet(sheetName);
        if (sheet == null) {
            return null;
        }
        CommResponse commRes = new CommResponse(true);
        //数据开始行
        int firstRowNum = 2;
        //列名所在行
        int hraderRowNum = 1;
        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
        //数据结束行
        int lastRowNum = sheet.getLastRowNum();
        List<Map<String, String>> columnCfglist = new ArrayList<>();
        String trade = MapUtil.getStr(param, "trade");
        if (StrUtil.equals(trade, "simple")) {
            columnCfglist = getSimpleTradeColumnCfglist();
        } else {
            columnCfglist = getPortfolioTradeColumnCfglist();
        }

        Row row = null;
        row = sheet.getRow(hraderRowNum - 1);
        //-------------------------------校验列名----------------------------
        for (int i = 0; i < columnCfglist.size(); i++) {
            Map columnCfgMap = columnCfglist.get(i);
            if (SysUtils.equals(columnCfgMap.get("isImport"), "1")) {
                Cell cell = row.getCell(i);
                if (cell == null) {
                    commRes.setSuccess(false);
                    commRes.setMsg("第" + hraderRowNum + "行" + (i + 1) + "列不能为空");
                    break;
                }
                CellValue cellValue = evaluator.evaluate(cell);
                String colImportName = columnCfgMap.get("headName").toString();
                if (!SysUtils.equals(cellValue.getStringValue(), colImportName)) {
                    commRes.setSuccess(false);
                    commRes.setMsg("第" + hraderRowNum + "行" + (i + 1) + "列值和配置名称不相同，请检查Excel列名！");
                    break;
                }
            }
        }
        if (!commRes.isSuccess()) {
            return commRes;
        }
        //-------------------------------处理数据----------------------------
        List<Map<String, Object>> data = new ArrayList<Map<String, Object>>();
        for (int rowIndex = (firstRowNum - 1); rowIndex <= lastRowNum; rowIndex++) {
            row = sheet.getRow(rowIndex);
            if (ExcelImportUtil.isRowEmpty(row)) {
                break;
            }
            Map<String, Object> rd = new LinkedHashMap<String, Object>();
            String str = "{";
            for (int i = 0; i < columnCfglist.size(); i++) {
                JSONObject json = new JSONObject();
                Map<String, String> columnCfgMap = columnCfglist.get(i);
                String colField = String.valueOf(columnCfgMap.get("colField"));
                int colLength = Integer.parseInt(String.valueOf(columnCfgMap.get("colLength")));
                String colType = String.valueOf(columnCfgMap.get("colType"));
                String cellValue = null;
                //判断是否为导入字段
                if (SysUtils.equals(columnCfgMap.get("isImport"), "1")) {
                    Cell cell = row.getCell(i);
                    cellValue = getCellValue(cell);
                    //是否需要转换
                    if (SysUtils.equals(columnCfgMap.get("isTransfer"), "1")) {
                        CommResponse commResponse = getTransferValue(columnCfgMap, cellValue, param);
                        if (commResponse.isSuccess()) {
                            cellValue = String.valueOf(commResponse.getMsg());
                        } else {
                            commRes.setSuccess(false);
                            commRes.setMsg("第" + (rowIndex + 1) + "行" + (i + 1) + "列值" + commResponse.getMsg() + "，请检查Excel！");
                            break;
                        }
                    }
                }
                //非空校验
                if (SysUtils.equals(columnCfgMap.get("allowBlank"), "0")) {
                    if (SysUtils.isVoid(cellValue)) {
                        commRes.setSuccess(false);
                        commRes.setMsg("第" + (rowIndex + 1) + "行" + (i + 1) + "列值不能为空，请检查Excel！");
                        break;
                    }
                }
                //长度校验
                if (cellValue.length() > colLength) {
                    commRes.setSuccess(false);
                    commRes.setMsg("第" + (rowIndex + 1) + "行" + (i + 1) + "列值长度超出限制，请检查Excel！");
                    break;
                }
                if (StrUtil.equals(colType, "NUMBER")) {
                    String reg = "-?[0-9]+(.[0-9]+)?";
                    if (!cellValue.matches(reg)) {
                        commRes.setSuccess(false);
                        commRes.setMsg("第" + (rowIndex + 1) + "行" + (i + 1) + "列值应为数值，请检查Excel！");
                    }
                }
                rd.put(colField, cellValue);
            }
            if (!commRes.isSuccess()) {
                break;
            }
            if (StrUtil.isNotBlank(MapUtil.getStr(rd, "iCode")) &&
                    StrUtil.isNotBlank(MapUtil.getStr(rd, "aType")) &&
                    StrUtil.isNotBlank(MapUtil.getStr(rd, "mType"))) {
                validInstruments(rd, param, rowIndex, commRes);
            }
            data.add(rd);
        }
        if (commRes.isSuccess()) {
            commRes.setMsg(data);
        }
        return commRes;
    }

    public CommResponse validInstruments(Map<String, Object> rd, Map param, int rowIndex, CommResponse commRes) {
        String iCode = MapUtil.getStr(rd, "iCode");
        String aType = MapUtil.getStr(rd, "aType");
        String mType = MapUtil.getStr(rd, "mType");
        String instrument = iCode + "," + aType + "," + mType;
        if (SysUtils.isNotVoid(cacheMap.get("instrument")) && cacheMap.get("instrument").contains(instrument)) {
            return commRes;
        }
        List list = instrumentMapper.getInstruments(rd);
        if (list.size() == 1) {
            if (SysUtils.isNotVoid(cacheMap.get("instrument"))) {
                cacheMap.get("instrument").add(instrument);
            } else {
                List lst = new LinkedList();
                lst.add(instrument);
                cacheMap.put("instrument", lst);
            }
        } else {
            commRes.setSuccess(false);
            commRes.setMsg("第" + rowIndex + "行标的不存在，请检查！");
        }
        return commRes;
    }

    public CommResponse getTransferValue(Map<String, String> columnCfgMap, String cellValue, Map param) {
        Object result = null;
        boolean tranfer = true;
        Map map = new HashMap();
        if (SysUtils.equals(columnCfgMap.get("colField"), "pCode")) {
            if (SysUtils.isNotVoid(cacheMap.get("pCode")) && cacheMap.get("pCode").contains(cellValue)) {
                result = cellValue;
            } else {
            }
        } else if (SysUtils.equals(columnCfgMap.get("colField"), "accId")) {
            //map.put("accName", cellValue);
            if (SysUtils.isVoid(cacheMap.get("account"))) {
                map.put("status", "1");
                map.put("accRight", "2");
                map.put("userId", SecurityUtils.getUserId().toString());
                List<Account> list = accountMapper.querySelfAccountUserPermByPage(map);
                cacheMap.put("account", list);
            }
            if (SysUtils.isNotVoid(cacheMap.get("account"))) {
                List<Account> list = cacheMap.get("account");
                boolean exist = false;
                for (int i = 0; i < list.size(); i++) {
                    if (StrUtil.equals(list.get(i).getAccName(), cellValue)) {
                        result = list.get(i).getAccName();
                        exist = true;
                        break;
                    }
                }
                if (!exist) {
                    tranfer = false;
                    result = "本方账户不存在或未启用";
                }
            } else {
                tranfer = false;
                result = "本方账户不存在或未启用";

            }


        } else if (SysUtils.equals(columnCfgMap.get("colField"), "tradeType")) {
            if (StrUtil.equals(MapUtil.getStr(param, "trade"), "simple")) {
                map.put("dictType", "simpleTradeType");
            } else {
                map.put("dictType", "portfolioTradeType");
            }
            map.put("dictValue", cellValue);

        } else if (SysUtils.equals(columnCfgMap.get("colField"), "aType")) {
            map.put("dictType", "A_TYPE");
            map.put("dictValue", cellValue);

        } else if (SysUtils.equals(columnCfgMap.get("colField"), "mType")) {
            map.put("dictType", "M_TYPE");
            map.put("dictValue", cellValue);

        } else if (SysUtils.equals(columnCfgMap.get("colField"), "currency")) {
            map.put("dictType", "Currency");
            map.put("dictValue", cellValue);

        }

        return new CommResponse(tranfer, result);
    }

    public static String getCellValue(Cell cell) {
        String cellValue = "";
        if (cell == null) {
            return cellValue;
        }
        // 判断数据的类型
        switch (cell.getCellType()) {
            // 数字
            case NUMERIC:
                //short s = cell.getCellStyle().getDataFormat();
                // 处理日期格式、时间格式
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat sdf = null;
                    String dateFmt = null;
                    // 验证short值
                    if (cell.getCellStyle().getDataFormat() == 14) {
                        dateFmt = "yyyy-MM-dd";
                    } else if (cell.getCellStyle().getDataFormat() == 21) {
                        dateFmt = "HH:mm:ss";
                    } else if (cell.getCellStyle().getDataFormat() == 22) {
                        dateFmt = "yyyy-MM-dd";
                    } else if (cell.getCellStyle().getDataFormat() == 176) {
                        dateFmt = "yyyy-MM-dd";
                    } else if (cell.getCellStyle().getDataFormat() == 177) {
                        dateFmt = "yyyy-MM-dd";
                    } else if (cell.getCellStyle().getDataFormat() == 165) {
                        dateFmt = "yyyy-MM-dd";
                    } else if (cell.getCellStyle().getDataFormat() == 166) {
                        dateFmt = "yyyy-MM-dd";
                    } else if (cell.getCellStyle().getDataFormat() == 167) {
                        dateFmt = "yyyy-MM-dd";
                    } else if (cell.getCellStyle().getDataFormat() == 168) {
                        dateFmt = "yyyy-MM-dd";
                    } else if (cell.getCellStyle().getDataFormat() == 169) {
                        dateFmt = "yyyy-MM-dd";
                    } else {
                        dateFmt = "yyyy-MM-dd";
                    }
                    sdf = new SimpleDateFormat(dateFmt);
                    Date date = cell.getDateCellValue();
                    cellValue = sdf.format(date);
                    //处理数值格式
                } else if (cell.getCellStyle().getDataFormat() == 0 ||
                        cell.getCellStyle().getDataFormat() == 176 ||
                        cell.getCellStyle().getDataFormat() == 177) {
                    cell.setCellType(CellType.forInt(com.microsoft.schemas.office.visio.x2012.main.CellType.GREATER_THAN));
                    cellValue = String.valueOf(cell.getRichStringCellValue().getString());
                } else {

                }
                break;
            // 字符串
            case STRING:
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            // Boolean
            case BOOLEAN:
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            // 公式
            case FORMULA:
                cellValue = String.valueOf(cell.getCellFormula());
                break;
            // 空值
            case BLANK:
                cellValue = null;
                break;
            // 故障
            case ERROR:
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }

    public List getSimpleTradeColumnCfglist() {
        List<Map<String, String>> columnCfglist = new ArrayList<>();
        Map<String, String> headMap = new HashMap();
        headMap.put("headName", "本方账户");
        headMap.put("colField", "accId");
        headMap.put("colType", "VARCHAR2");
        headMap.put("colLength", "200");
        headMap.put("allowBlank", "0");
        headMap.put("isImport", "1");
        headMap.put("isTransfer", "1");
        columnCfglist.add(headMap);
        headMap = new HashMap();
        headMap.put("headName", "交易日期");
        headMap.put("colField", "tradeDate");
        headMap.put("colType", "VARCHAR2");
        headMap.put("colLength", "100");
        headMap.put("allowBlank", "0");
        headMap.put("isImport", "1");
        headMap.put("isTransfer", "0");
        columnCfglist.add(headMap);
        headMap = new HashMap();
        headMap.put("headName", "交易类型");
        headMap.put("colField", "tradeType");
        headMap.put("colType", "VARCHAR2");
        headMap.put("colLength", "100");
        headMap.put("allowBlank", "0");
        headMap.put("isImport", "1");
        headMap.put("isTransfer", "1");
        columnCfglist.add(headMap);
        headMap = new HashMap();
        headMap.put("headName", "资产代码");
        headMap.put("colField", "iCode");
        headMap.put("colType", "VARCHAR2");
        headMap.put("colLength", "100");
        headMap.put("allowBlank", "0");
        headMap.put("isImport", "1");
        headMap.put("isTransfer", "0");
        columnCfglist.add(headMap);
        headMap = new HashMap();
        headMap.put("headName", "资产类型");
        headMap.put("colField", "aType");
        headMap.put("colType", "VARCHAR2");
        headMap.put("colLength", "200");
        headMap.put("allowBlank", "0");
        headMap.put("isImport", "1");
        headMap.put("isTransfer", "1");
        columnCfglist.add(headMap);
        headMap = new HashMap();
        headMap.put("headName", "市场类型");
        headMap.put("colField", "mType");
        headMap.put("colType", "VARCHAR2");
        headMap.put("colLength", "100");
        headMap.put("allowBlank", "0");
        headMap.put("isImport", "1");
        headMap.put("isTransfer", "1");
        columnCfglist.add(headMap);
        headMap = new HashMap();
        headMap.put("headName", "数量");
        headMap.put("colField", "volume");
        headMap.put("colType", "NUMBER");
        headMap.put("colLength", "50");
        headMap.put("allowBlank", "0");
        headMap.put("isImport", "1");
        headMap.put("isTransfer", "0");
        columnCfglist.add(headMap);
        headMap = new HashMap();
        headMap.put("headName", "价格");
        headMap.put("colField", "price");
        headMap.put("colType", "NUMBER");
        headMap.put("colLength", "10");
        headMap.put("allowBlank", "0");
        headMap.put("isImport", "1");
        headMap.put("isTransfer", "0");
        columnCfglist.add(headMap);
        headMap = new HashMap();
        headMap.put("headName", "金额");
        headMap.put("colField", "amount");
        headMap.put("colType", "NUMBER");
        headMap.put("colLength", "30");
        headMap.put("allowBlank", "0");
        headMap.put("isImport", "1");
        headMap.put("isTransfer", "0");
        columnCfglist.add(headMap);
        headMap = new HashMap();
        headMap.put("headName", "费用");
        headMap.put("colField", "fee");
        headMap.put("colType", "NUMBER");
        headMap.put("colLength", "30");
        headMap.put("allowBlank", "0");
        headMap.put("isImport", "1");
        headMap.put("isTransfer", "0");
        columnCfglist.add(headMap);
        headMap = new HashMap();
        headMap.put("headName", "币种");
        headMap.put("colField", "currency");
        headMap.put("colType", "VARCHAR2");
        headMap.put("colLength", "30");
        headMap.put("allowBlank", "0");
        headMap.put("isImport", "1");
        headMap.put("isTransfer", "1");
        columnCfglist.add(headMap);
        return columnCfglist;
    }

    public List getPortfolioTradeColumnCfglist() {
        List<Map<String, String>> columnCfglist = new ArrayList<>();
        Map<String, String> headMap = new HashMap();
        headMap.put("headName", "合约编号");
        headMap.put("colField", "pCode");
        headMap.put("colType", "VARCHAR2");
        headMap.put("colLength", "200");
        headMap.put("allowBlank", "0");
        headMap.put("isImport", "1");
        headMap.put("isTransfer", "1");
        columnCfglist.add(headMap);
        headMap = new HashMap();
        headMap.put("headName", "交易日期");
        headMap.put("colField", "tradeDate");
        headMap.put("colType", "VARCHAR2");
        headMap.put("colLength", "100");
        headMap.put("allowBlank", "0");
        headMap.put("isImport", "1");
        headMap.put("isTransfer", "0");
        columnCfglist.add(headMap);
        headMap = new HashMap();
        headMap.put("headName", "交易类型");
        headMap.put("colField", "tradeType");
        headMap.put("colType", "VARCHAR2");
        headMap.put("colLength", "100");
        headMap.put("allowBlank", "0");
        headMap.put("isImport", "1");
        headMap.put("isTransfer", "1");
        columnCfglist.add(headMap);
        headMap = new HashMap();
        headMap.put("headName", "资产代码");
        headMap.put("colField", "iCode");
        headMap.put("colType", "VARCHAR2");
        headMap.put("colLength", "100");
        headMap.put("allowBlank", "0");
        headMap.put("isImport", "1");
        headMap.put("isTransfer", "0");
        columnCfglist.add(headMap);
        headMap = new HashMap();
        headMap.put("headName", "资产类型");
        headMap.put("colField", "aType");
        headMap.put("colType", "VARCHAR2");
        headMap.put("colLength", "200");
        headMap.put("allowBlank", "0");
        headMap.put("isImport", "1");
        headMap.put("isTransfer", "1");
        columnCfglist.add(headMap);
        headMap = new HashMap();
        headMap.put("headName", "市场类型");
        headMap.put("colField", "mType");
        headMap.put("colType", "VARCHAR2");
        headMap.put("colLength", "100");
        headMap.put("allowBlank", "0");
        headMap.put("isImport", "1");
        headMap.put("isTransfer", "1");
        columnCfglist.add(headMap);
        headMap = new HashMap();
        headMap.put("headName", "数量");
        headMap.put("colField", "volume");
        headMap.put("colType", "NUMBER");
        headMap.put("colLength", "50");
        headMap.put("allowBlank", "0");
        headMap.put("isImport", "1");
        headMap.put("isTransfer", "0");
        columnCfglist.add(headMap);
        headMap = new HashMap();
        headMap.put("headName", "价格");
        headMap.put("colField", "price");
        headMap.put("colType", "NUMBER");
        headMap.put("colLength", "10");
        headMap.put("allowBlank", "0");
        headMap.put("isImport", "1");
        headMap.put("isTransfer", "0");
        columnCfglist.add(headMap);
        headMap = new HashMap();
        headMap.put("headName", "金额");
        headMap.put("colField", "amount");
        headMap.put("colType", "NUMBER");
        headMap.put("colLength", "30");
        headMap.put("allowBlank", "0");
        headMap.put("isImport", "1");
        headMap.put("isTransfer", "0");
        columnCfglist.add(headMap);
        headMap = new HashMap();
        headMap.put("headName", "费用");
        headMap.put("colField", "fee");
        headMap.put("colType", "NUMBER");
        headMap.put("colLength", "30");
        headMap.put("allowBlank", "0");
        headMap.put("isImport", "1");
        headMap.put("isTransfer", "0");
        columnCfglist.add(headMap);
        headMap = new HashMap();
        headMap.put("headName", "币种");
        headMap.put("colField", "currency");
        headMap.put("colType", "VARCHAR2");
        headMap.put("colLength", "30");
        headMap.put("allowBlank", "0");
        headMap.put("isImport", "1");
        headMap.put("isTransfer", "1");
        columnCfglist.add(headMap);
        headMap = new HashMap();
        headMap.put("headName", "券源");
        headMap.put("colField", "dimSource");
        headMap.put("colType", "VARCHAR2");
        headMap.put("colLength", "30");
        headMap.put("allowBlank", "1");
        headMap.put("isImport", "1");
        headMap.put("isTransfer", "0");
        columnCfglist.add(headMap);
        headMap = new HashMap();
        headMap.put("headName", "利率");
        headMap.put("colField", "dimIr");
        headMap.put("colType", "NUMBER");
        headMap.put("colLength", "30");
        headMap.put("allowBlank", "1");
        headMap.put("isImport", "1");
        headMap.put("isTransfer", "0");
        columnCfglist.add(headMap);
        headMap = new HashMap();
        headMap.put("headName", "到期日");
        headMap.put("colField", "dimExpireDate");
        headMap.put("colType", "VARCHAR2");
        headMap.put("colLength", "30");
        headMap.put("allowBlank", "1");
        headMap.put("isImport", "1");
        headMap.put("isTransfer", "0");
        columnCfglist.add(headMap);
        headMap = new HashMap();
        headMap.put("headName", "唯一编号");
        headMap.put("colField", "dimGuid");
        headMap.put("colType", "VARCHAR2");
        headMap.put("colLength", "300");
        headMap.put("allowBlank", "1");
        headMap.put("isImport", "1");
        headMap.put("isTransfer", "0");
        columnCfglist.add(headMap);
        headMap = new HashMap();
        headMap.put("headName", "备注");
        headMap.put("colField", "remark");
        headMap.put("colType", "VARCHAR2");
        headMap.put("colLength", "300");
        headMap.put("allowBlank", "1");
        headMap.put("isImport", "1");
        headMap.put("isTransfer", "0");
        columnCfglist.add(headMap);
        return columnCfglist;
    }

}
